Create a PQL formula to add to queries to see the yearly sales variance. To review PQL functions, click here.
The syntax of this formula will be:
([measures].[data Sales])-([measures].[data Sales],PrevMember([data].[dateKey year]))
STEP 1
Build a custom member in Formula, using two data points. The first data point should contain the Sales measure. Add a minus sign, and then add the second data point.
STEP 2
To the second data point, add the Sales measure, and then open the dateKey Year hierarchy. Instead of selecting a year from the Elements panel, click the tree icon next to the search tool.
STEP 3
Select Previous Member from the Elements panel.
STEP 4
Click the Pyramid button to preview the formula syntax. Then, save the custom member, or click the Quick Discovery button.
STEP 5
Open a new discovery and add: dateKey Year, Sales, and the new custom member.
